package org.test.ant;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.bouncycastle.util.encoders.Base64;

public class UpgradeTo3 {
    private static final String SDK = "sdk";
    private static final String LOCAL = "local";
    private static final String SEPARATOR = ",";
    private static final File UPGRADE3_FILE = new File("upgrade3.sql");
    private static BufferedWriter out = null;
    private static final int ID_USER_FLAG = 1;
    private static final int ID_DICT_FLAG = 2;
    private static final int ID_USER_CACHE_FLAG = 3;
    private static final int ID_GROUP_FLAG = 4;
    private static final int ID_ROLE_FLAG = 5;
    private static int ID_USER_COUNT = 0;
    private static int ID_DICT_COUNT = 0;
    private static int ID_USER_CACHE_COUNT = 4;
    private static int ID_GROUP_COUNT = 99;
    private static int ID_ROLE_COUNT = 99;

    public static Map<String, Integer> rights = new HashMap();

    public static Map<String, UpgradeTo3.RoleMetaData> targetType = new HashMap();
    public static Map<String, UpgradeTo3.RoleMetaData> memberType = new HashMap();
    public static Map<String, String> sources = new HashMap();
    public static Map<String, Integer> userIDs = new HashMap();
    public static Map<String, Integer> groupIDs = new HashMap();

    static {
        try {
            out = new BufferedWriter(new OutputStreamWriter(
                    new FileOutputStream(UPGRADE3_FILE), "UTF-8"), 16384);

            rights.put("7", Integer.valueOf(7));

            targetType.put("user",
                    new UpgradeTo3.RoleMetaData(Integer.valueOf(0), "user"));
            targetType.put("depa",
                    new UpgradeTo3.RoleMetaData(Integer.valueOf(1), "depa"));
            targetType
                    .put("allUser",
                            new UpgradeTo3.RoleMetaData(Integer.valueOf(3),
                                    "allusers"));
            targetType.put("roster",
                    new UpgradeTo3.RoleMetaData(Integer.valueOf(4), "roster"));
            memberType.put("user",
                    new UpgradeTo3.RoleMetaData(Integer.valueOf(0), "user"));
            memberType.put("depa",
                    new UpgradeTo3.RoleMetaData(Integer.valueOf(1), "depa"));
            memberType
                    .put("allUser",
                            new UpgradeTo3.RoleMetaData(Integer.valueOf(3),
                                    "allusers"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void init() throws Exception {
        writeToFile(null, " delete from rtpGroup ;", new Object[0]);
        writeToFile(null, " delete from rtpGroupUser ;", new Object[0]);
        writeToFile(null, " delete from rtpID;", new Object[0]);
        writeToFile(null, " delete from rtpIdDict;", new Object[0]);
        writeToFile(null, " delete from rtpProperty;", new Object[0]);
        writeToFile(null, " delete from rtpRole;", new Object[0]);
        writeToFile(null, " delete from rtpRoleMember;", new Object[0]);
        writeToFile(null, " delete from rtpRoleRight ;", new Object[0]);
        writeToFile(null, " delete from rtpRoleTarget ;", new Object[0]);
        writeToFile(null, " delete from rtpSource ;", new Object[0]);
        writeToFile(null, " delete from rtpSyncUserCache ;", new Object[0]);
        writeToFile(null, " delete from rtpUser ;", new Object[0]);
        writeToFile(null, " delete from rtpUserProp ;", new Object[0]);
        writeToFile(null, " delete from rtpVCardPhoto ;", new Object[0]);
        Connection con = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        try {
            con = DBManager.getConnection();
            stat = con
                    .prepareStatement("SELECT table_name   FROM information_schema.tables where table_type='BASE TABLE'  and  engine='InnoDB'  and (table_name in ('vcard','user','spool','roomrating','roomcategory','private_storage','invita_message','group_config','awappro_group') or table_name like 'of%') and table_name not in('ofuser','ofuserflag','ofuserprop','ofversion')");

            rs = stat.executeQuery();
            while (rs.next())
                writeToFile(null, " delete from " + rs.getString("table_name")
                        + " ;", new Object[0]);
        } finally {
            DBManager.closeConnection(con, stat, rs);
        }
        writeToFile("以下为要导入的数据", null, new Object[0]);
    }

    public static void dealSourceTable() throws Exception {
        Connection con = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        String insertTemplate = "INSERT INTO rtpSource (sourceName,sourceType,sourceTitle,props) value('%1$s','%2$s','%3$s','%4$s');";
        String sourceName = null;
        String sourceType = null;
        String url = null;
        String domainID = null;
        StringBuffer props = null;
        try {
            writeToFile("导出源表", null, new Object[0]);
            con = DBManager.getConnection();
            stat = con
                    .prepareStatement("select ID_,WEB_ID_,DESC_,URL_ from tb_rtp_domain order by   ADD_DATE_ asc");
            rs = stat.executeQuery();
            while (rs.next()) {
                domainID = rs.getString("ID_");
                sourceName = rs.getString("WEB_ID_");
                url = rs.getString("URL_");
                props = new StringBuffer();
                props.append("digestAlgorithm=md5");
                props.append(",");
                if (("default".equalsIgnoreCase(sourceName))
                        || ("superAdmin".equalsIgnoreCase(sourceName))) {
                    sourceType = "local";
                    sourceName = "local";
                } else {
                    sourceType = "sdk";
                    props.append("url=" + ((url == null) ? "" : url));
                    props.append(",");
                }

                stat = con
                        .prepareStatement("select PROP_NAME_ , PROP_VALUE_ from tb_rtp_domain_ext where DOMAIN_ID_ =?");
                stat.setString(1, domainID);
                ResultSet rs2 = stat.executeQuery();
                while (rs2.next()) {
                    props.append(rs2.getString("PROP_NAME_")
                            + "="
                            + ((rs2.getString("PROP_VALUE_") == null) ? ""
                                    : rs2.getString("PROP_VALUE_")));
                    props.append(",");
                }
                deleteTail(props, 1);
                writeToFile(null, insertTemplate, new Object[] { sourceName,
                        sourceType, sourceName, props.toString() });

                sources.put(domainID, sourceName);
            }
        } finally {
            DBManager.closeConnection(con, stat, rs);
        }
    }

    public static void dealPropertyTable() throws Exception {
        Connection con = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        String insertTemplate = "INSERT INTO rtpProperty (name,propValue) value('%1$s','%2$s');";
        try {
            writeToFile("导属性表", null, new Object[0]);
            con = DBManager.getConnection();
            stat = con
                    .prepareStatement("select NAME_,VALUE_ from tb_rtp_property  where NAME_ in ('rtpGroupAddress','rtpGroupPort')");
            rs = stat.executeQuery();
            while (rs.next()) {
                writeToFile(
                        null,
                        insertTemplate,
                        new Object[] { rs.getString("NAME_"),
                                rs.getString("VALUE_") });
            }
            writeToFile(null, insertTemplate, new Object[] {
                    "rtp.source.default", "local" });
            writeToFile(null, insertTemplate, new Object[] { "passwordKey",
                    "7C4A8D09CA3762AF61E59520943DC26494F8941B" });
        } finally {
            DBManager.closeConnection(con, stat, rs);
        }
    }

    public static void dealOtherTable() throws Exception {
        Connection con = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        StringBuffer insertTemplate = null;
        StringBuffer valueStr = null;
        Object value = null;
        List rowNames = new ArrayList();
        try {
            writeToFile("导其他表", null, new Object[0]);
            con = DBManager.getConnection();
            stat = con
                    .prepareStatement("SELECT table_name   FROM information_schema.tables where table_type='BASE TABLE'  and  engine='InnoDB'  and (table_name in ('vcard','user','spool','roomrating','roomcategory','private_storage','invita_message','group_config','awappro_group') or table_name like 'of%') and table_name not in('ofuser','ofuserflag','ofuserprop','ofversion')");

            rs = stat.executeQuery();
            while (rs.next()) {
                rowNames.clear();
                insertTemplate = new StringBuffer("INSERT INTO ");
                stat = con.prepareStatement("SELECT * from "
                        + rs.getString("table_name"));
                ResultSet rs2 = stat.executeQuery();
                ResultSetMetaData rsmd = rs2.getMetaData();
                int rowCount = rsmd.getColumnCount() + 1;
                insertTemplate.append(rs.getString("table_name"));
                insertTemplate.append("(");
                for (int i = 1; i < rowCount; ++i) {
                    rowNames.add(rsmd.getColumnName(i));
                    insertTemplate.append(rsmd.getColumnName(i));
                    insertTemplate.append(",");
                }
                deleteTail(insertTemplate, 1);
                insertTemplate.append(") ");
                insertTemplate.append(" VALUE ");
                while (rs2.next()) {
                    valueStr = new StringBuffer();
                    valueStr.append("(");
                    for (int i = 0; i < rowNames.size(); ++i) {
                        value = rs2.getObject((String) rowNames.get(i));
                        if (value instanceof String)
                            valueStr.append(String.format("'%1$s'",
                                    new Object[] { value }));
                        else {
                            valueStr.append(value);
                        }
                        valueStr.append(",");
                    }
                    deleteTail(valueStr, 1);
                    valueStr.append(");");
                    writeToFile(null, insertTemplate + valueStr.toString(),
                            new Object[0]);
                }
            }
        } finally {
            DBManager.closeConnection(con, stat, rs);
        }
    }

    public static void dealIDTable() throws Exception {
        String insertTemplate = "INSERT INTO rtpID (id, idType) VALUES (%1$d, %2$d);";
        writeToFile("导RTPID表", null, new Object[0]);
        writeToFile(
                null,
                insertTemplate,
                new Object[] { Integer.valueOf(ID_USER_COUNT),
                        Integer.valueOf(1) });
        writeToFile(
                null,
                insertTemplate,
                new Object[] { Integer.valueOf(ID_GROUP_COUNT),
                        Integer.valueOf(4) });
        writeToFile(
                null,
                insertTemplate,
                new Object[] { Integer.valueOf(ID_DICT_COUNT),
                        Integer.valueOf(2) });
        writeToFile(
                null,
                insertTemplate,
                new Object[] { Integer.valueOf(ID_ROLE_COUNT),
                        Integer.valueOf(5) });
        writeToFile(
                null,
                insertTemplate,
                new Object[] { Integer.valueOf(ID_USER_CACHE_COUNT),
                        Integer.valueOf(3) });
    }

    public static void dealUserTable() throws Exception {
        Connection con = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        String insertTemplate = "INSERT INTO rtpUser (userID, source, userName, password, state,jid)VALUES (%1$d,'%2$s','%3$s','%4$s',%5$d,'%6$s');";
        String insertTemplate2 = "INSERT INTO rtpUserProp (userID, propName, propValue)VALUES (%1$d,'%2$s','%3$s');";
        String insertTemplate3 = "INSERT into rtpVCardPhoto(userId,photo) values(%1$d,'%2$s');";
        String insertTemplate4 = "INSERT INTO rtpIdDict (itemID, userID, source, userName)VALUES (%1$d,%2$d,'%3$s','%4$s');";
        String source = null;
        String userName = null;
        String password = null;
        String nickname = null;
        String fullname = null;
        String email = null;
        int state = 0;
        String jid = null;
        String photo = null;
        try {
            writeToFile("导用户表", null, new Object[0]);
            con = DBManager.getConnection();
            stat = con
                    .prepareStatement("select ID_, JID_,PASSWORD_,USERNAME_,WEB_ID_,NICKNAME_,FULLNAME_,EMAIL_ from tb_rtp_user ");
            rs = stat.executeQuery();
            while (rs.next()) {
                userName = rs.getString("USERNAME_");
                password = rs.getString("PASSWORD_");
                if ((password != null) && (!(password.equals("")))) {
                    password = binToHex(Base64.decode(password));
                }
                jid = rs.getString("JID_");
                source = rs.getString("WEB_ID_");
                nickname = rs.getString("NICKNAME_");
                fullname = rs.getString("FULLNAME_");
                email = rs.getString("EMAIL_");
                if ("default".equalsIgnoreCase(source)) {
                    source = "local";
                }
                int userId = ++ID_USER_COUNT + 10000;
                writeToFile(
                        null,
                        insertTemplate,
                        new Object[] { Integer.valueOf(userId), source,
                                userName, password, Integer.valueOf(state), jid });
                userIDs.put(rs.getString("ID_"), Integer.valueOf(userId));
                writeToFile(null, insertTemplate4,
                        new Object[] { Integer.valueOf(++ID_DICT_COUNT),
                                Integer.valueOf(userId), source, userName });
                if ((fullname != null) && (!("".equals(fullname)))) {
                    writeToFile(null, insertTemplate2,
                            new Object[] { Integer.valueOf(userId), "fullname",
                                    fullname });
                }
                if ((nickname != null) && (!("".equals(nickname)))) {
                    writeToFile(null, insertTemplate2,
                            new Object[] { Integer.valueOf(userId), "nickname",
                                    nickname });
                }
                if ((email != null) && (!("".equals(email)))) {
                    writeToFile(null, insertTemplate2,
                            new Object[] { Integer.valueOf(userId), "email",
                                    email });
                }
                stat = con
                        .prepareStatement("select PROP_NAME_,PROP_VALUE_  from tb_rtp_user_ext where USER_ID_=? ");
                stat.setString(1, rs.getString("ID_"));
                ResultSet rs2 = stat.executeQuery();
                while (rs2.next()) {
                    if ("photo".equalsIgnoreCase(rs2.getString("PROP_NAME_"))) {
                        photo = rs2.getString("PROP_VALUE_");
                    } else {
                        writeToFile(
                                null,
                                insertTemplate2,
                                new Object[] { Integer.valueOf(userId),
                                        rs2.getString("PROP_NAME_"),
                                        rs2.getString("PROP_VALUE_") });
                    }
                }
                if (photo != null)
                    writeToFile(null, insertTemplate3,
                            new Object[] { Integer.valueOf(userId), "photo",
                                    photo });
            }
        } finally {
            DBManager.closeConnection(con, stat, rs);
        }
    }

    public static void dealGroupTable() throws Exception {
        Connection con = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        String insertTemplate = "INSERT INTO rtpGroup (groupID, parentId, groupName, source) VALUES (%1$d,%2$d,'%3$s','%4$s');";
        String insertTemplate2 = "INSERT INTO rtpGroupUser (groupID, userId)  VALUES (%1$d,%2$d);";
        String groupName = null;
        String source = null;
        String code = null;
        int parentId = 0;
        int groupLevel = 0;
        try {
            writeToFile("导组织表", null, new Object[0]);
            con = DBManager.getConnection();
            stat = con
                    .prepareStatement("select ID_,NAME_,CODE_,TYPE_,DOMAIN_ID_ from tb_rtp_department order by CODE_ asc");
            rs = stat.executeQuery();
            while (rs.next()) {
                groupName = rs.getString("NAME_");
                source = (String) sources.get(rs.getString("DOMAIN_ID_"));
                code = rs.getString("CODE_");

                if (("0001".equals(code)) || ("00010001".equals(code))) {
                    if ("0001".equals(code))
                        groupIDs.put(rs.getString("ID_"), Integer.valueOf(0));
                    else
                        groupIDs.put(rs.getString("ID_"), Integer.valueOf(1));
                } else {
                    groupLevel = code.length() / 4 - 2;
                    if (groupLevel == 0) {
                        parentId = 0;
                    } else {
                        stat = con
                                .prepareStatement("select ID_,NAME_,CODE_,TYPE_,DOMAIN_ID_ from tb_rtp_department where DOMAIN_ID_=? and CODE_=? ");
                        stat.setString(1, rs.getString("DOMAIN_ID_"));
                        stat.setString(2, code.substring(0, code.length() - 4));
                        ResultSet rs2 = stat.executeQuery();
                        if (rs2.next()) {
                            if (!(groupIDs.containsKey(rs2.getString("ID_")))) {
                                throw new RuntimeException("no parentId");
                            }
                            parentId = ((Integer) groupIDs.get(rs2
                                    .getString("ID_"))).intValue();
                        }
                    }
                    writeToFile(null, insertTemplate,
                            new Object[] { Integer.valueOf(++ID_GROUP_COUNT),
                                    Integer.valueOf(parentId), groupName,
                                    source, Integer.valueOf(groupLevel) });
                    groupIDs.put(rs.getString("ID_"),
                            Integer.valueOf(ID_GROUP_COUNT));

                    stat = con
                            .prepareStatement("select USER_ID_ from tb_rtp_user_bid_depa where DEPA_ID_=?");
                    stat.setString(1, rs.getString("ID_"));
                    ResultSet rs2 = stat.executeQuery();
                    while (rs2.next())
                        writeToFile(
                                null,
                                insertTemplate2,
                                new Object[] { Integer.valueOf(ID_GROUP_COUNT),
                                        userIDs.get(rs2.getString("USER_ID_")) });
                }
            }
        } finally {
            DBManager.closeConnection(con, stat, rs);
        }
    }

    public static void dealRoleTable() throws Exception {
        Connection con = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        String insertTemplate = "INSERT INTO rtpRole (roleID, roleName, roleDescr) VALUES (%1$d,'%2$s','%3$s');";
        String insertTemplate2 = "INSERT INTO rtpRoleRight (roleID, rightID) VALUES (%1$d,%2$d);";
        String insertTemplate3 = "INSERT INTO rtpRoleTarget(roleID, rightID, target, targetType) VALUES (%1$d,%2$d,'%3$s',%4$d);";
        String insertTemplate4 = "INSERT INTO rtpRoleMember (roleID, member, memberType)VALUES (%1$d,'%2$s',%3$d);";
        String roleName = null;
        String roleDescr = null;
        try {
            writeToFile("导角色表", null, new Object[0]);
            con = DBManager.getConnection();

            stat = con.prepareStatement("select ID_,NAME_ from tb_rtp_rights ");
            rs = stat.executeQuery();
            String rightsName = null;
            while (rs.next()) {
                rightsName = rs.getString("NAME_");
                if ("RTP_I18N_rights_name_roster".equalsIgnoreCase(rightsName)) {
                    rights.put(rs.getString("ID_"), Integer.valueOf(0));
                } else if ("RTP_I18N_rights_name_changeInfo"
                        .equalsIgnoreCase(rightsName)) {
                    rights.put(rs.getString("ID_"), Integer.valueOf(6));
                } else if ("RTP_I18N_rights_name_video"
                        .equalsIgnoreCase(rightsName)) {
                    rights.put(rs.getString("ID_"), Integer.valueOf(3));
                } else if ("RTP_I18N_rights_name_privateFirend"
                        .equalsIgnoreCase(rightsName)) {
                    rights.put(rs.getString("ID_"), Integer.valueOf(5));
                } else if ("RTP_I18N_rights_name_fileTransfer"
                        .equalsIgnoreCase(rightsName)) {
                    rights.put(rs.getString("ID_"), Integer.valueOf(1));
                } else if ("RTP_I18N_rights_name_group_create"
                        .equalsIgnoreCase(rightsName)) {
                    rights.put(rs.getString("ID_"), Integer.valueOf(9));
                } else if ("RTP_I18N_rights_name_group_applyjoin"
                        .equalsIgnoreCase(rightsName)) {
                    rights.put(rs.getString("ID_"), Integer.valueOf(8));
                } else if ("RTP_I18N_rights_name_audio"
                        .equalsIgnoreCase(rightsName)) {
                    rights.put(rs.getString("ID_"), Integer.valueOf(2));
                } else if ("RTP_I18N_rights_name_remoteHelp"
                        .equalsIgnoreCase(rightsName)) {
                    rights.put(rs.getString("ID_"), Integer.valueOf(4));
                }
            }

            stat = con
                    .prepareStatement("select ID_ ,NAME_,SUMMARY_ from tb_rtp_role  order by SIGN_ desc , ADD_DATE_ asc");
            rs = stat.executeQuery();

            while (rs.next()) {
                roleName = rs.getString("NAME_");
                roleDescr = rs.getString("SUMMARY_");
                int roleId = 0;
                if ("RTP_I18N_role_everyone".equalsIgnoreCase(roleName)) {
                    continue;
                }
                writeToFile(null, insertTemplate,
                        new Object[] { Integer.valueOf(++ID_ROLE_COUNT),
                                roleName, roleDescr });
                roleId = ID_ROLE_COUNT;

                stat = con
                        .prepareStatement("select ID_ ,RIGHTS_ID_ from tb_rtp_role_bid_rights where  ROLE_ID_=? ");
                stat.setString(1, rs.getString("ID_"));
                ResultSet rs2 = stat.executeQuery();
                while (rs2.next()) {
                    writeToFile(
                            null,
                            insertTemplate2,
                            new Object[] { Integer.valueOf(roleId),
                                    rights.get(rs2.getString("RIGHTS_ID_")) });
                }

                stat = con
                        .prepareStatement("select t1.ID_,t1.RIGHTS_ID_,t2.USER_ID_,t2.DEPA_ID_,t2.TYPE_ from tb_rtp_role_bid_rights as t1,tb_rtp_role_target as t2  where  t1.ROLE_ID_=? and t2.ROLE_RIGHTS_ID_=t1.ID_ ");
                stat.setString(1, rs.getString("ID_"));
                rs2 = stat.executeQuery();
                UpgradeTo3.RoleMetaData roleMetaData;
                while (rs2.next()) {
                    roleMetaData = (UpgradeTo3.RoleMetaData) targetType.get(rs2
                            .getString("TYPE_"));
                    if (rs2.getString("TYPE_").equalsIgnoreCase("roster"))
                        writeToFile(
                                null,
                                insertTemplate3,
                                new Object[] {
                                        Integer.valueOf(roleId),
                                        rights.get(rs2.getString("RIGHTS_ID_")),
                                        roleMetaData.getName(),
                                        roleMetaData.getId() });
                    else if (rs2.getString("TYPE_").equalsIgnoreCase("allUser"))
                        writeToFile(
                                null,
                                insertTemplate3,
                                new Object[] {
                                        Integer.valueOf(roleId),
                                        rights.get(rs2.getString("RIGHTS_ID_")),
                                        roleMetaData.getName(),
                                        roleMetaData.getId() });
                    else if (rs2.getString("TYPE_").equalsIgnoreCase("user"))
                        writeToFile(
                                null,
                                insertTemplate3,
                                new Object[] {
                                        Integer.valueOf(roleId),
                                        rights.get(rs2.getString("RIGHTS_ID_")),
                                        userIDs.get(rs2.getString("USER_ID_")),
                                        roleMetaData.getId() });
                    else if (rs2.getString("TYPE_").equalsIgnoreCase("depa")) {
                        writeToFile(
                                null,
                                insertTemplate3,
                                new Object[] {
                                        Integer.valueOf(roleId),
                                        rights.get(rs2.getString("RIGHTS_ID_")),
                                        groupIDs.get(rs2.getString("DEPA_ID_")),
                                        roleMetaData.getId() });
                    }
                }

                stat = con
                        .prepareStatement("select t1.ID_,t1.USER_ID_,t1.DEPA_ID_,t1.TYPE_ from tb_rtp_role_belonger as t1   where  t1.ROLE_ID_=?  ");
                stat.setString(1, rs.getString("ID_"));
                rs2 = stat.executeQuery();
                while (rs2.next()) {
                    roleMetaData = (UpgradeTo3.RoleMetaData) memberType.get(rs2
                            .getString("TYPE_"));
                    if (rs2.getString("TYPE_").equalsIgnoreCase("roster"))
                        writeToFile(null, insertTemplate4, new Object[] {
                                Integer.valueOf(roleId),
                                roleMetaData.getName(), roleMetaData.getId() });
                    else if (rs2.getString("TYPE_").equalsIgnoreCase("allUser"))
                        writeToFile(null, insertTemplate4, new Object[] {
                                Integer.valueOf(roleId),
                                roleMetaData.getName(), roleMetaData.getId() });
                    else if (rs2.getString("TYPE_").equalsIgnoreCase("user"))
                        writeToFile(
                                null,
                                insertTemplate4,
                                new Object[] { Integer.valueOf(roleId),
                                        userIDs.get(rs2.getString("USER_ID_")),
                                        roleMetaData.getId() });
                    else if (rs2.getString("TYPE_").equalsIgnoreCase("depa"))
                        writeToFile(
                                null,
                                insertTemplate4,
                                new Object[] {
                                        Integer.valueOf(roleId),
                                        groupIDs.get(rs2.getString("DEPA_ID_")),
                                        roleMetaData.getId() });
                }
            }
        } finally {
            DBManager.closeConnection(con, stat, rs);
        }
    }

    public static void over() throws Exception {
        if (out != null)
            out.close();
    }

    private static void writeToFile(String comment, String template,
            Object[] args) throws Exception {
        if (comment != null) {
            out.append("-- " + comment);
            out.newLine();
        }
        if (template != null) {
            if ((args != null) && (args.length > 0)) {
                for (int i = 0; i < args.length; ++i) {
                    if ((args[i] instanceof String) && (args[i] == null)) {
                        args[i] = "";
                    }
                }
            }
            out.append(String.format(template, args));
            out.newLine();
        }
    }

    private static void deleteTail(StringBuffer props, int index) {
        if (props.length() > 0)
            props.delete(props.length() - index, props.length());
    }

    private static String binToHex(byte[] bin) {
        if (bin == null)
            return "";
        StringBuffer buf = new StringBuffer(bin.length * 2);
        for (int i = 0; i < bin.length; ++i) {
            int d = 0xFF & bin[i];
            if (d < 16) {
                buf.append('0');
            }
            buf.append(Integer.toHexString(d).toUpperCase());
        }
        return buf.toString();
    }

    public static void main(String[] args) throws Exception {
        try {
            System.out.println("init....");
            init();
            System.out.println("dealPropertyTable....");
            dealPropertyTable();
            System.out.println("dealSourceTable....");
            dealSourceTable();
            System.out.println("dealUserTable....");
            dealUserTable();
            System.out.println("dealGroupTable....");
            dealGroupTable();
            System.out.println("dealRoleTable....");
            dealRoleTable();
            System.out.println("dealIDTable....");
            dealIDTable();
            System.out.println("dealOtherTable....");
            dealOtherTable();
        } finally {
            System.out.println("over....");
            over();
        }
    }

    private static class RoleMetaData {
        private Integer id;
        private String name;

        public RoleMetaData(Integer id, String name) {
            super();
            this.id = id;
            this.name = name;
        }

        public Integer getId() {
            return id;
        }

        public String getName() {
            return name;
        }

    }

}
